# -*- coding:utf-8 -*-
"""
-------------------------------------------------
   File Name:      reader.py
   Author:         luzhongfu
   date:           2020/05/15
-------------------------------------------------
"""
from typing import Any
import openpyxl
import os
from utils import common
from utils import logs


class ExcelReader:
    def __init__(self) -> None:
        self.datas = {}
        self.fields_column = {}  # key={filename}.{title}; value = column_num
        self.cache = {'columns_value': {}}
        self.log = logs.LogHandler(__name__)

    def add_file(self, path: str) -> None:
        basename = os.path.basename(path)
        filename = os.path.splitext(basename)[0]
        if self.datas.get(filename):
            raise ValueError('文件已添加过: %s' % filename)

        source_data = openpyxl. \
            load_workbook(path,read_only=True,data_only=True).active
        self.datas[filename] = source_data

        # 获取表头(第一行)
        # 当获取为空时结束
        column = 0
        while True:
            column += 1
            title = source_data.cell(row=1, column=column).value
            if not title:
                break

            title = str(title).strip()
            field = self.build_field(filename, title)
            self.fields_column[field] = column

    def add_mem_file(self, filename, source_data):
        if self.datas.get(filename):
            raise ValueError('文件已添加过: %s' % filename)

        self.datas[filename] = source_data

        column = 0
        while True:
            column += 1
            title = source_data.cell(row=1, column=column).value
            # print(f'column: {column} title: {title}')
            if not title:
                break

            title = str(title).strip()
            field = self.build_field(filename, title)
            self.fields_column[field] = column

        # print('fields_column: ', self.fields_column)

    def get_field_value(self, field: str, row: int) -> Any:
        field = common.field_strip(field)

        column = self.fields_column.get(field)
        if not column:
            raise ValueError('字段不存在: %s' % field)

        filename, title = self.parse_field(field)
        source = self.datas.get(filename)
        return source.cell(row=row, column=column).value

    def parse_field(self, field: str):
        values = field.split('.')
        if len(values) != 2:
            # print("field: ", field)
            raise ValueError(f'解析字段 {field} 失败')
        filename = values[0]
        title = values[1]
        return filename, title

    def get_file_columns(self, filename, title):
        field = self.build_field(filename, title)
        source = self.datas.get(filename)
        column = self.fields_column.get(field)
        if column is None:
            raise ValueError('列不存在: %s' % field)

        values = self.cache['columns_value'].get(f'{field}:{column}')
        if values is not None:
            return values
        values = []
        row = 1  # 忽略标题
        while True:
            row += 1
            value = source.cell(row, column).value
            if not value:
                break
            values.append(value)

        self.cache['columns_value'][f'{field}:{column}'] = values
        return values

    def get_field_columns(self, field):
        filename, title = self.parse_field(field)
        return self.get_file_columns(filename, title)

    def build_field(self, filename, title):
        return '{}.{}'.format(filename, title)


if __name__ == '__main__':
    reader = ExcelReader()
    reader.add_file('./sources_data/源数据.xlsx')
    reader.add_file('./sources_data/基础数据.xlsx')

    print(reader.get_field_value('源数据.发票号码', 3))
    print(reader.get_file_columns('源数据', '客户类别'))